<?php
//统计
namespace app\ausagent\controller;
use app\ausagent\controller\Common;
use think\Request;
use think\Db;
use think\Session;
use think\Cookie;
use think\Controller;
use think\Config;
use Godok\Org\Auth;

 class Statistics extends Common{

 	public function index(){
 		
 		$time = "thismonth";
 		if(input('time')){
 			$time =  input('time');
 		}
 		if($time == 'thisyear' || $time=='lastyear'){
 			$day = "'%m'";
 			$unit = '月';
 		}else {
 			$unit = '日';
 			$day = "'%d'";
 		}
 		$kaishitime = strtotime(input('starttime'));
    	$jieshutime = strtotime(input('endtime'));
    	//自定义时间段统计查询
    	if(!empty($kaishitime) and !empty($jieshutime)){
    		//$w = " create_time >= $kaishitime and create_time <= $jieshutime ";//初始化时间段
    		$where = array(
    			'oid'			=>	session('oid'),
    			'is_delete' 	=>	1,
    			
    		);
    		$w = " create_time >= $kaishitime and create_time <= $jieshutime ";
	        $day= "'%m月%d'";
	        $time = '';
	        $this->assign('kaishitime',input('starttime'));
	        $this->assign('jieshutime',input('endtime'));
	        //充值金额统计
	        $Recharge = Db::name('rechargeorder')
	        		  ->where($where)
	        		  ->where($w)
	        		  ->where('balance','>',0)
		              ->where('pay_status',1)
	                  ->field("FROM_UNIXTIME(create_time,$day) as unit,sum(real_amount) as amount")
	                  ->group("unit")
	                  ->select(); 
	        //echo Db::name('rechargeorder')->getlastsql(); var_dump($Recharge);die;
	        $sumrecharge = Db::name('rechargeorder')  
	        			 ->where($where)
		                 ->where('balance','neq','')
		                 ->sum('real_amount'); 
		    //echo $sumrecharge;die;
		    //租金统计
	        $leaseorder = Db::name('leaseorder')
	                  ->where($where)
	        		  ->where($w)
	                  ->where(['status'=>2])
	                  ->field("FROM_UNIXTIME(create_time,$day) as unit,ifnull(sum(real_amount),0) as amount")
	                  ->group("unit")
	                  ->select(); 
	        $sumleaseorder = Db::name('leaseorder')
	        			   ->where($where)
	        		  	   ->where($w)
	                  	   ->where(['status'=>2])
	                  	   ->sum('real_amount');
	        //押金退款 
	        //$re['b.refund_time'] = array(array('egt',$times['starttime']),array('elt',$times['endtime']), 'and');         
	        $refund = DB::name('rechargeorder')
        		->alias('a')
                ->join("refund b",'a.oid = b.oid and a.order_no = b.order_no')
                ->where("a.create_time >= $kaishitime and a.create_time <= $jieshutime")
                ->where(['a.oid'=>session('oid'),'a.is_delete'=>1,'a.pay_status'=>1])
                ->where('a.refunddeposit','>',0)
        		->field("FROM_UNIXTIME(b.refund_time,$day) as unit,ifnull(sum(a.refunddeposit),0) as amount")
        		->group("unit")
                ->select();
            $sumrefund =  DB::name('rechargeorder')
            		   ->alias('a')
		               ->join("refund b",'a.oid = b.oid and a.order_no = b.order_no')
		               ->where(['a.oid'=>session('oid'),'a.is_delete'=>1,'a.pay_status'=>1])
		               ->where("a.create_time >= $kaishitime and a.create_time <= $jieshutime")
		               ->where('a.refunddeposit','>',0) 
		               ->sum('a.refunddeposit');        	   
		    //押金充值统计
        	$depositRecharge = DB::name('rechargeorder')
			    			 ->where($where)
			    			 ->where($w)
			    			 ->where('trade_no' ,'neq', '')
			    			 ->where('deposit','>',0)
			    			 ->where('pay_status',1)
			    			 ->field("FROM_UNIXTIME(create_time,$day) as unit,ifnull(sum(deposit),0) as deposit")
			    			 ->group('unit')
			    			 ->select(); 
			$sumdepositRecharge = DB::name('rechargeorder')
			    			 	->where($where)
			    			 	->where($w)
			    			 	->where('trade_no' ,'neq', '')
			    			 	->where('deposit','>',0)
			    			 	->where('pay_status',1)
			    			 	->sum('deposit') ;         
			//用户统计
			$user = DB::name('user')
	 			  ->where($where)
	 			  ->where($w)
	 			  ->field("FROM_UNIXTIME(create_time,$day) as unit,ifnull(count(distinct openid),0) as count")
	 			  ->group('unit')
	    		  ->select(); 
	    	$countuser = DB::name('user')
	 			  	   ->where($where)  
	 			  	   ->where($w) 			 	
	 			  	   ->count('distinct openid') ;
	 		//商家经营状况
	 		$sellers = Db::name('seller')
                -> alias('a')
                ->join("leaseorder b",'a.id = b.sid')
                ->field("sum(b.real_amount) as amount,a.name as name")
                ->where('a.is_delete',1)
                ->where("a.create_time >= $kaishitime and a.create_time <= $jieshutime")
                ->where('a.oid',session('oid'))
                ->group("name")
                ->select();	 
            $sumsellers = Db::name('seller') 
            			-> alias('a')
                		->join("leaseorder b",'a.id = b.sid')	   
                		->where('a.is_delete',1)
		                ->where("a.create_time >= $kaishitime and a.create_time <= $jieshutime")
		                ->where('a.oid',session('oid'))
		                ->sum('b.real_amount');


    	}else{//默认时间统计查询
 		//充值金额统计
 		$times = gettimestamp($time);
 		$rsearch['oid'] = session('oid');
        $rsearch['is_delete'] = 1;
        $rsearch['payment_date'] = array(array('egt',$times['starttime']),array('elt',$times['endtime']), 'and');
        $Recharge = Db::name('rechargeorder')
                  ->where($rsearch)
                  ->where('balance','>',0)
                  ->where('pay_status',1)
                  ->field("FROM_UNIXTIME(payment_date,$day) as unit,sum(real_amount) as amount")
                  ->group("unit")
                  ->select();
        $sumrecharge = Db::name('rechargeorder')  
        			 ->where($rsearch)
        			 ->where('pay_status',1)
	                 ->where('balance','>',0)
	                 ->sum('real_amount');  
 		//租金统计
 		$wl['oid'] = session('oid');
        $wl['is_delete'] = 1;
        $wl['create_time'] = array(array('egt',$times['starttime']),array('elt',$times['endtime']), 'and');
        $leaseorder = Db::name('leaseorder')
                  ->where($wl)
                  ->where(['status'=>2])
                  ->field("FROM_UNIXTIME(create_time,$day) as unit,ifnull(sum(real_amount),0) as amount")
                  ->group("unit")
                  ->select();
        $sumleaseorder = Db::name('leaseorder')
        			   ->where($wl)
                  	   ->where(['status'=>2])
                  	   ->sum('real_amount');
        //押金退款统计
        /* 数据查询参照
		SELECT FROM_UNIXTIME(create_time,'%d') as unit,ifnull(sum(amount),0) as amount FROM `leaseorder` WHERE `oid` = 1 AND `is_delete` = 1 AND ( `create_time` >= 1496246400 and `create_time` <= 1498838399 ) GROUP BY unit
         */
        
        $b = 'refund';
        $re['a.oid'] = session('oid');
        $re['a.is_delete'] = 1;
        $re['b.refund_time'] = array(array('egt',$times['starttime']),array('elt',$times['endtime']), 'and');
        //$re = "a.oid = ".session('oid')." AND a.is_delete = 1 AND b.refund_time >=".$time['starttime']." AND b.refund_time <= ".$time['endtime'];
        $refund = DB::name('rechargeorder')
        		->alias('a')
                ->join("$b b",'a.oid = b.oid and a.order_no = b.order_no')
        		->where($re)
        		->where('a.refunddeposit','>',0) 
        		->where('a.pay_status',1)
        		->field("FROM_UNIXTIME(b.refund_time,$day) as unit,ifnull(sum(a.refunddeposit),0) as amount")
        		->group("unit")
                ->select();
        $sumrefund =  DB::name('rechargeorder')
        		   ->alias('a')
	               ->join("refund b",'a.oid = b.oid and a.order_no = b.order_no')
	               ->where('a.pay_status',1)
	               ->where($re)
	               ->where('a.refunddeposit','>',0) 
	               ->sum('a.refunddeposit');         
        //押金充值统计
        $depositRecharge = DB::name('rechargeorder')
    			->where($wl)
    			->where('trade_no' ,'neq', '')
    			->where('deposit','>',0)
		    	->where('pay_status',1)
    			->field("FROM_UNIXTIME(create_time,$day) as unit,ifnull(sum(deposit),0) as deposit")
    			->group('unit')
    			->select();
    	$sumdepositRecharge = DB::name('rechargeorder')
		    			 	->where($wl)
		    			 	->where('trade_no' ,'neq', '')
		    			 	->where('deposit','>',0)
		    			 	->where('pay_status',1)
		    			 	->sum('deposit') ;
 		//用户统计
 		$user = DB::name('user')
 			  ->where($wl)
 			  ->field("FROM_UNIXTIME(create_time,$day) as unit,ifnull(count(distinct openid),0) as count")
 			  ->group('unit')
    		  ->select();
    	$countuser = DB::name('user')
 			  	   ->where($wl)   			 	
 			  	   ->count('distinct openid') ;	  
    	//商户统计
    	$s = 'leaseorder';
    	$seller['a.oid'] = session('oid');
        $seller['a.is_delete'] = 1;
        $seller['b.create_time'] = array(array('egt',$times['starttime']),array('elt',$times['endtime']), 'and');
		$sellers = Db::name('seller')
                -> alias('a')
                ->join("$s b",'a.id = b.sid')
                ->where($seller) 
                ->field("sum(b.real_amount) as amount,a.name as name")
                ->group("name")
                ->select();
        $sumsellers = Db::name('seller') 
        			-> alias('a')
            		->join("leaseorder b",'a.id = b.sid')	   
            		->where('a.is_delete',1)
	                ->where(['b.create_time'=>array(array('egt',$times['starttime']),array('elt',$times['endtime']), 'and')])
	                ->where('a.oid',session('oid'))
	                ->sum('b.real_amount');
 		//商户统计
 		}
 		$data=array(
 			'Recharge' 				=> $Recharge,
 			'leaseorder' 			=> $leaseorder,
 			'refund'     			=> $refund,
 			'depositRecharge' 		=> $depositRecharge,
 			'user'       			=> $user,
 			'sellers'    			=> $sellers,
 			'sumrecharge' 			=> $sumrecharge,
 			'sumleaseorder'			=> $sumleaseorder,
 			'sumrefund'				=> $sumrefund,
 			'sumdepositRecharge'	=> $sumdepositRecharge,
 			'countuser'				=> $countuser,
 			'sumsellers'			=> $sumsellers,
 		);
 		$this->assign('unit',$unit);
 		$this->assign('time',$time);
 		$this->assign('data',$data);
 		
 		return $this->fetch();
 	}
 }